package com.xjrsoft.common.dbmodel.utils;

import com.baomidou.mybatisplus.annotation.DbType;
import com.xjrsoft.common.dbmodel.entity.TableField;
import com.xjrsoft.common.dbmodel.entity.TableInfo;
import com.xjrsoft.core.tool.utils.StringPool;
import com.xjrsoft.core.tool.utils.StringUtil;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;

public class SqlUtil {

    private SqlUtil() {
    }

    public static List<String> buildCreateTableSql(DbType dbType, List<TableInfo> tableInfoList) {
        List<String> sqlList = new ArrayList<>();
        for (TableInfo tableInfo : tableInfoList) {
            sqlList.addAll(buildCreateTableSql(dbType, tableInfo));
        }
        return sqlList;
    }

    public static List<String> buildCreateTableSql(DbType dbType, TableInfo tableInfo) {
        String sql = StringPool.EMPTY;
        List<String> sqlList = new ArrayList<>();
        if (dbType == DbType.MYSQL) {
            sql = getMysqlCreateTableSql(tableInfo);
        } else if (dbType == DbType.ORACLE || dbType == DbType.ORACLE_12C) {
            sql = getOracleCreateTableSql(tableInfo);
        } else if (dbType == DbType.SQL_SERVER || dbType == DbType.SQL_SERVER2005){
            sqlList.addAll(getSqlServerCreateTableSql(tableInfo));
        } else if (dbType == DbType.POSTGRE_SQL) {
            sqlList.addAll(getPgSqlCreateTableSql(tableInfo));
        } else if (dbType == DbType.KINGBASE_ES) {
            sqlList.addAll(getPgSqlCreateTableSql(tableInfo));
        }
        if (!StringUtil.isEmpty(sql)) {
            sqlList.add(sql);
        }
        if (DbType.ORACLE == dbType) {
            List<String> oracleTableCommentSqls = getOracleTableCommentSqls(tableInfo);
            oracleTableCommentSqls.stream().forEach(oracleSql -> sqlList.add(oracleSql));
        }
        return sqlList;
    }

    private static List<String> getPgSqlCreateTableSql(TableInfo tableInfo) {
        List<String> resultSqlList = new ArrayList<>();
        StringBuilder createSql = new StringBuilder();
        String keySql = StringPool.EMPTY;
        String lowerTableName = StringUtils.lowerCase(tableInfo.getName());
        createSql.append("CREATE TABLE " + lowerTableName + " (");
        for (TableField field : tableInfo.getFields()) {
            Integer length = field.getLength();
            String dataType = getPgSqlDataType(field.getType());
            String lowerFieldName = StringUtils.lowerCase(field.getName());
            createSql.append(lowerFieldName + " " + dataType);
            if (StringUtils.equalsIgnoreCase(dataType, "varchar")) {
                createSql.append("(" + (length == null ? "250" : length) + ")");
            }
            if (BooleanUtils.isTrue(field.getIsKey())) {
                keySql = "CONSTRAINT " + lowerTableName + "_pk PRIMARY KEY (" + lowerFieldName + ")";
            }
            if (BooleanUtils.isTrue(field.getIsNullable())) {
                createSql.append(" NOT NULL ");
            }
            createSql.append(",");
        }
        if (StringUtil.isEmpty(keySql)) {
            createSql.delete(createSql.length() - 1, createSql.length());
        } else {
            createSql.append(keySql);
        }
        createSql.append(");");
        resultSqlList.add(createSql.toString());

        for (TableField field : tableInfo.getFields()) {
            String remark = field.getComment();
            if (StringUtil.isNotBlank(remark)) {
                // 添加列备注
                resultSqlList.add("COMMENT ON COLUMN " + tableInfo.getName() + "." + field.getName() + " IS '" + remark + "'");
            }
        }

        // 添加表备注
        if (StringUtil.isNotBlank(tableInfo.getComment())) {
            resultSqlList.add("COMMENT ON TABLE " + tableInfo.getName() + " IS '" + tableInfo.getComment() + "'");
        }
        return resultSqlList;
    }

    /**
     * @return 建表SQL语句
     * @author 湘北智造-框架开发组
     * @date：2019年10月14日 @description：构建sqlserver数据库创建表sql
     */
    private static List<String> getSqlServerCreateTableSql(TableInfo tableInfo) {
        List<String> resultSqlList = new ArrayList<>();
        StringBuilder createSql = new StringBuilder();
        createSql.append("CREATE TABLE " + tableInfo.getName() + " (");
        for (TableField field : tableInfo.getFields()) {
            Integer length = field.getLength();
            String dataType = getSqlServerDataType(field.getType());
            if(StringUtil.equalsIgnoreCase(dataType,"longtext")){
                //长文本类型
                dataType="nvarchar(max)";
            }
            createSql.append(field.getName() + " " + dataType);
            if (StringUtils.equalsIgnoreCase(dataType, "varchar")
                    || StringUtils.equalsIgnoreCase(dataType, "nvarchar")) {
                createSql.append("(" + (length == null ? "250" : length) + ")");
            } else if(StringUtil.equalsIgnoreCase(dataType, "decimal")) {
                createSql.append("(" + (length == null ? "18" : (length > 18 ? 18 : length)) + ", 2)");
            }
            if (BooleanUtils.isTrue(field.getIsKey())) {
                createSql.append(" PRIMARY KEY ");
            } else if (BooleanUtils.isTrue(field.getIsNullable())) {
                createSql.append(" NOT NULL ");
            }
            createSql.append(",");
        }
        createSql.delete(createSql.length() - 1, createSql.length());
        createSql.append(");");
        resultSqlList.add(createSql.toString());

        for (TableField field : tableInfo.getFields()) {
            String remark = field.getComment();
            if (StringUtil.isNotBlank(remark)) {
                // 添加列备注
                resultSqlList.add(" execute sp_addextendedproperty 'MS_Description','" + remark + "','user','dbo','table','"
                        + tableInfo.getName() + "','column','" + field.getName() + "';");
            }
        }

        // 添加表备注
        if (StringUtil.isNotBlank(tableInfo.getComment())) {
            resultSqlList.add(" execute sp_addextendedproperty 'MS_Description','" + tableInfo.getComment() + "','user','dbo','table','"
                    + tableInfo.getName() + "',null,null;  ");
        }
        return resultSqlList;
    }

    /**
     * @return 建表SQL语句
     * @author 湘北智造-框架开发组
     * @date：2019年10月14日 @description：构建mysql数据库创建表sql
     */
    private static String getMysqlCreateTableSql(TableInfo tableInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE " + tableInfo.getName() + " ( ");// 表名
        for (TableField field : tableInfo.getFields()) {
            Integer length = field.getLength();
            String dataType = getMysqlDataType(field.getType());
            sql.append(field.getName() + " " + dataType);// 列名+类型
            if (StringUtils.equalsIgnoreCase(dataType, "varchar")) {
                sql.append("(" + (length == null ? "250" : length) + ") ");// 长度
            } else if(StringUtil.equalsIgnoreCase(dataType, "decimal")) {
                sql.append("(" + (length == null ? "18" : (length > 18 ? 18 : length)) + ", 2)");
            } else if (length != null && StringUtils.equalsIgnoreCase(dataType, "int")) {
                sql.append("(" + (length > 11 ? "11" : length) + ") ");// 长度
            }
            if (BooleanUtils.isTrue(field.getIsKey())) {
                sql.append(" PRIMARY KEY ");// 是否主键
            } else if (BooleanUtils.isTrue(field.getIsNullable())) {
                sql.append(" NOT NULL ");// 是否为空
            }
            String remark = field.getComment();
            if (StringUtil.isNotBlank(remark)) {
                sql.append(" COMMENT '" + remark + "'");// 列备注
            }
            sql.append(",");
        }
        sql.delete(sql.length() - 1, sql.length());
        sql.append(" )");
        // 添加表备注
        if (!StringUtil.isBlank(tableInfo.getComment())) {
            sql.append(" ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='" + tableInfo.getComment() + "';");
        }

        return sql.toString();
    }

    private static String getOracleCreateTableSql(TableInfo tableInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE " + tableInfo.getName().toUpperCase() + " ( ");// 表名
        for (TableField field : tableInfo.getFields()) {
            Integer length = field.getLength();
            String type = getOracleDataType(field.getType());
            if (StringUtils.equalsIgnoreCase(field.getType(), "longtext")) {
                length = 2000;
                type = "NVARCHAR2";
            }
            sql.append("\"" + field.getName().toUpperCase() + "\" " + type);// 列名+类型
            if (StringUtils.equalsIgnoreCase(type, "VARCHAR2") ||
                    StringUtils.equalsIgnoreCase(type, "NVARCHAR2")) {

                sql.append("(" + (length == null ? "250" : length) + ") ");// 长度
            } else if(StringUtil.equalsIgnoreCase(field.getType(), "double")) {
                sql.append("(" + (length == null ? "18" : (length > 18 ? 18 : length)) + ", 6)");
            } else if (length != null && StringUtils.equalsIgnoreCase(type, "NUMBER")) {
                sql.append("(" + (length > 11 ? "11" : length) + ") ");// 长度
            }
            if (BooleanUtils.isTrue(field.getIsKey())) {
                sql.append(" PRIMARY KEY NOT NULL ");// 是否主键
            } else if (BooleanUtils.isTrue(field.getIsNullable())) {
                sql.append(" NOT NULL ");// 是否为空
            } else {
                sql.append(" NULL ");// 是否为空
            }
            sql.append(",");
        }
        sql.delete(sql.length() - 1, sql.length());
        sql.append(" )");
        return sql.toString();
    }

    public static List<String> getOracleTableCommentSqls(TableInfo tableInfo) {
        List<String> sqls = new ArrayList<String>();
        //// 添加表备注
        if (StringUtil.isNotBlank(tableInfo.getComment())) {
            sqls.add(" COMMENT ON TABLE " + StringUtils.upperCase(tableInfo.getName()) + " is '" + tableInfo.getComment() + "'  ");
        }
        for (TableField field : tableInfo.getFields()) {
            String remark = field.getComment();
            if (StringUtil.isNotBlank(remark)) {
                // 添加列备注
                sqls.add(" COMMENT ON COLUMN " + tableInfo.getName().toUpperCase() + ".\""
                        + StringUtils.upperCase(field.getName()) + "\" is '" + remark + "'");
            }
        }
        return sqls;
    }

    private static String getOracleDataType(String dataType) {
        String res = dataType;
        switch (StringUtils.lowerCase(dataType)) {
            case "varchar2":
            case "nvarchar2":
            case "varchar":
                res = "NVARCHAR2";
                break;
            case "longtext":
                res = "NCLOB";
                break;
            case "datetime":
                res = "DATE";
                break;
            case "int":
            case "double":
                res = "NUMBER";
                break;
        }
        return res;
    }

    private static String getMysqlDataType(String dataType) {
        String res = dataType;
        switch (dataType) {
            case "longtext":
                res = "text";
                break;
            case "double":
                res = "decimal";
                break;
        }
        return res;
    }

    private static String getSqlServerDataType(String dataType) {
        String res = dataType;
        switch (dataType) {
            case "longtext":
                res = "text";
                break;
            case "double":
                res = "decimal";
                break;
        }
        return res;
    }

    private static String getPgSqlDataType(String dataType) {
        String res = dataType;
        switch (StringUtils.lowerCase(dataType)) {
            case "longtext":
                res = "text";
                break;
            case "int":
                res = "int4";
                break;
            case "double":
                res = "numeric";
                break;
            case "datetime":
                res = "timestamp";
        }
        return res;
    }

    public static String buildDeleteTableSql(String tableName) {
        return "DROP TABLE " + tableName;
    }

    public static String getDataType(DbType dbType, String type) {
        String dataType = type;
        if (dbType == DbType.MYSQL) {
            dataType = getMysqlDataType(type);
        } else if (dbType == DbType.ORACLE || dbType == DbType.ORACLE_12C) {
            dataType = getOracleDataType(type);
        } else if (dbType == DbType.SQL_SERVER || dbType == DbType.SQL_SERVER2005){
            dataType = getSqlServerDataType(type);
        } else if (dbType == DbType.POSTGRE_SQL || dbType == DbType.KINGBASE_ES) {
            dataType = getPgSqlDataType(type);
        }
        return dataType;
    }
}
